﻿/****************
* Creat Date: 2020/08/04 03:55:26
* Creator: Quick.CodeGenerator
*****************/
using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using BookStores.Entity;
using SqlSugar;
using System.Threading.Tasks;
using BookStores.DTO.Request;

namespace BookStores.Repositories
{
    /// <summary>
    /// 数据表操作实体类：Books
    /// </summary>
    [Serializable]
    public partial class BooksRepository : QuickFramework.SqlSugarOrm.Repository<BookStores.Entity.Books>, BookStores.Contracts.IBooksRepository
    {
        /// <summary>
        /// 构造方法,初始化数据库链接：Books
        /// </summary>
        public BooksRepository() : base(QuickFramework.SqlSugarOrm.DbConnectionStrings.Default)
        { }
        /// <summary>
        /// 构造方法,初始化数据库链接：Books
        /// </summary>
        public BooksRepository(string dbConnectionString) : base(dbConnectionString)
        {

        }

        /// <summary>
        /// 根据参数查询数据
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<(List<BookStores.Entity.Books>, int)> Query(BookQueryParam param)
        {
            string sqlWhere = "";
            string tmpSqlWhere = "";
            List<SqlSugar.SugarParameter> parameters = new List<SugarParameter>();
            if (!string.IsNullOrEmpty(param?.KeyWord))
            {
                if (!string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere += " AND ";
                }
                tmpSqlWhere = " MATCH(Book_Name) AGAINST(@kwName IN BOOLEAN MODE) ";
                parameters.Add(new SugarParameter("@kwName", param.KeyWord + "*"));

                tmpSqlWhere += " OR MATCH(Book_Brief) AGAINST(@kwBrief IN BOOLEAN MODE) ";
                parameters.Add(new SugarParameter("@kwBrief", param.KeyWord + "*"));

                tmpSqlWhere += " OR Book_SerialNo LIKE @Book_SerialNo ";
                parameters.Add(new SugarParameter("@Book_SerialNo", "%" + param.KeyWord + "%"));

                tmpSqlWhere += " OR Book_Barcode LIKE @Book_Barcode ";
                parameters.Add(new SugarParameter("@Book_Barcode", "%" + param.KeyWord + "%"));

                sqlWhere += $"({tmpSqlWhere})";
            }
            if (null != param.Book_Tag && param.Book_Tag.Length > 0)
            {
                if (!string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere += " AND ";
                }
                sqlWhere += $"( MATCH(Book_Tag) AGAINST(@Book_Tag))";
                parameters.Add(new SugarParameter("@Book_Tag", string.Join(',', param.Book_Tag)));
            }
            if (null != param.Book_Language && param.Book_Language.Length > 0)
            {
                if (!string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere += " AND ";
                }
                sqlWhere += $"( MATCH(Book_Language) AGAINST(@Book_Language))";
                parameters.Add(new SugarParameter("@Book_Language", string.Join(',', param.Book_Language)));
            }
            if (!string.IsNullOrEmpty(param.Book_Publish_Status))
            {
                if (!string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere += " AND ";
                }
                sqlWhere += $"( Book_Publish_Status = @Book_Publish_Status ) ";
                parameters.Add(new SugarParameter("@Book_Publish_Status", param.Book_Publish_Status));
            }
            if (!string.IsNullOrEmpty(param.Book_Recommend_Status))
            {
                if (!string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere += " AND ";
                }
                sqlWhere += $"( Book_Recommend_Status = @Book_Recommend_Status ) ";
                parameters.Add(new SugarParameter("@Book_Recommend_Status", param.Book_Recommend_Status));
            }
            if (null != param.Book_Author && param.Book_Author.Length > 0)
            {
                if (!string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere += " AND ";
                }
                sqlWhere += $"( MATCH(Book_Author) AGAINST(@Book_Author))";
                parameters.Add(new SugarParameter("@Book_Author", string.Join(',', param.Book_Author)));
            }
            if (!string.IsNullOrEmpty(param.Book_Publishing))
            {
                if (!string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere += " AND ";
                }
                sqlWhere += $"( Book_Publishing = @Book_Publishing ) ";
                parameters.Add(new SugarParameter("@Book_Publishing", param.Book_Publishing));
            }
            if (null != param.Book_Series && param.Book_Series.Length > 0)
            {
                if (!string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere += " AND ";
                }
                sqlWhere += $"( MATCH(Book_Series) AGAINST(@Book_Series))";
                parameters.Add(new SugarParameter("@Book_Series", string.Join(',', param.Book_Series)));
            }

            if (!string.IsNullOrEmpty(sqlWhere))
            {
                sqlWhere += " AND ";
            }
            sqlWhere += " IsActive='Y' ";

            var queryable = base.Queryable().Where(sqlWhere, parameters);

            int count = await queryable.CountAsync();

            var result = await queryable.OrderBy(t => t.CreatedOn, OrderByType.Desc).ToPageListAsync(param.page, param.limit);

            return (result, count);
        }
    }
}
